Google Sheets URL: https://docs.google.com/spreadsheets/d/13sXTPf1_qdOAjoDYG250jAS6ped0E3-2DLasms26N3M/
Premise
I’ve always wanted to workout properly, and ever since I’ve discovered bouldering I’ve finally found my motivation to start. However, since I was overseas and had a lot of freetime and wanted to structure my workouts well, I decided to make my own workout spreadsheet. My only goals were to make it pretty, make it easy to use, and make it easy to update the backend.
How it Works
The functionality of the spreadsheet is spread across a few different sequences that data flows through. (BCY stands for the bouldering, calisthenics, and yoga pages)
BCY (workouts) -> BCY (catalogue) -> exercise list Interface + exercise list -> backend Backend + Microcycle -> Macrocycle Graph (inputs) + backend -> Backend2 -> Graph (chart)
The first sequence conglomerates each exercise and its corresponding data into one exercise list. The main functions used in this sequence are written in google apps script. One combines multiple tables together from a set of ranges, while the other returns the header of a table from the exercise name. It’s important to note that the catalogue stores the data on which muscle groups and exercise types correspond to a set exercise, so both the catalogue and the workouts below need to be updated with each exercise.
The second sequence starts with the interface, which references the appropriate exercise and its data from the exercise list and updates the backend. This is also done in google apps script, and works by checking for updates in the submit checkbox, and when an update is found it clears the interface and moves the data into a new row in the backend tab. A little quality of life feature I added was automatically using the user’s last bodyweight as the weight if none is filled in, which makes inputting bodyweight exercises smoother. The sets value also defaults to the value in the exercise list page if left blank.
The interface page uses checkboxes instead of buttons because I had trouble using them on my phone, and that’s the main way I use the spreadsheet. The update backend button simply forcefully updates the entire first sequence, since google sheets doesn’t automatically do so and it is often broken when first loading the spreadsheet. Lastly, the Update bodyweight button prompts the user for their weight and adds a new row to the BackendWeight page.
The third sequence uses the backend and microcycle as data for the macrocycle. The microcycle is just used to tell how many sessions of bouldering/calisthenics/yoga are needed to turn the corresponding cell green. Rows 5-9 of the macrocycle page are dedicated to splitting the page into a year month date format. It works by having a row of dates representing each sunday. From this, it can find the year or month of each week. Something to note is that it only displays the year if it is the first week of the year, so that only one 2025 is shown instead of one for each week. The same is true for months. However, this created another issue where the year cell could not spill over into neighbouring cells to display the full year because, although the neighbouring cells were blank, they still contained a formula and couldn’t be overflowed into. To fix this, I replaced the formulas with a single arrayformula, which would leave the blank cells truly empty as the formula was in the single cell containing the arrayformula instead of the empty cell itself.
The cells to calculate the data on the macrocycle were hell to code, and it would be needlessly complex to explain each one, but if you’re interested, you can open the spreadsheet and try to understand them for yourself. (The indirect command is used with a string of the range because google sheets changes A7:A to A8:A when adding a new row, even if $ is used)
Where do I even start :(
The last sequence starts with the graph page referencing the appropriate exercise from the backend and updating the values in backend2, where the data is formatted in a way that google sheets can graph using a pivot table, and finally is graphed in the graph page.
How to Use
Changing the Exercises
To change an exercise, you go to the corresponding page(ex. calisthenics) and edit the workouts by adding exercises and their corresponding sets. This will then update the catalogue, though the muscle groups and exercise types need to be manually selected afterwards in the catalogue. To add more workouts than the three given, you can add more ranges to the ranges list on the right of the catalogue.
Adding Entries to the Interface
This is done by simply selecting the category and exercise from the drop down lists, filling in the sets, reps, and weight (sets and weight are optional, defaulting to the sets value in the exercise list and your last bodyweight if left blank), and pressing submit. Sometimes on the phone, pressing the submit button doesn’t work on the first try, as with some of the other buttons, so it may be necessary to uncheck and recheck it again. (Though give it some time, the functions take a while because of how apps script works)
Changing the Microcycle
A cell automatically turns blue and gets counted if you type anything inside, and goes blank and removed from the count if you delete what’s inside.
Conclusion
Honestly, I’m quite happy with how it turned out, since it looks nice and is easy to use, but there’s also plenty of ways it could be improved. It struggles when the same exercise is put in multiple different workouts, the metrics it uses in the macrocycle page are not the most well calculated, the backend breaks almost every time the spreadsheet is loaded, the update backend button takes ten years to run, changing the exercises requires shifting all of the exercise type and muscle group data around, and more. Regardless, I’m quite lazy but I still use it without much self-control, so I’m happy with it.